﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QuanLyThuVien.DTO;
using System.Data;
using QuanLyThuVien.Supporter;
using System.Data.SqlClient;

namespace QuanLyThuVien.DAL
{
    //----------------------------------------------------------------------
    //Desc: class kết nối với bảng CUONSACH
    //----------------------------------------------------------------------
    public class DALCuonSach
    {
        //----------------------------------------------------------------------
        //Desc: 
        //----------------------------------------------------------------------
        ///
        //----------------------------------------------------------------------
        //Desc: constructor mặc định
        //----------------------------------------------------------------------
        public DALCuonSach()
        { }

        //----------------------------------------------------------------------
        //Desc: đếm số cuốn sách có cùng mã sách đang được mượn
        //----------------------------------------------------------------------
        public int DemSoCuonSachDangMuon(string masach)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spSach_DemSoSachDangMuon";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = masach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaSach);
            if (obj == null)
                return -1;
            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: lấy mã thể loại
        //----------------------------------------------------------------------
        public string LayMaTL(string macuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spCuonSach_LayMaTL";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaCuonSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            return data.Rows[0][0].ToString();
        }

        //----------------------------------------------------------------------
        //Desc: lấy tên thể loại
        //----------------------------------------------------------------------
        public string LayTenTL(string macuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spCuonSach_LayTenTL";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaCuonSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            return data.Rows[0][0].ToString();
        }

        //----------------------------------------------------------------------
        //Desc: lấy thông tin cuốn sách
        //----------------------------------------------------------------------
        public DTOCuonSach LayThongTinCuonSach(string macuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spCuonSach_LayThongTinCuonSach";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaCuonSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            DTOCuonSach cuonsach = new DTOCuonSach();
            cuonsach.MaCuonSach = data.Rows[0]["MaCuonSach"].ToString();
            cuonsach.MaSach = data.Rows[0]["MaSach"].ToString();
            try
            {
                cuonsach.TinhTrang = (bool)data.Rows[0]["TinhTrang"];
            }
            catch { }

            return cuonsach;
        }

        //----------------------------------------------------------------------
        //Desc: đếm số cuốn sách của sách
        //----------------------------------------------------------------------
        public int DemCuonSach(string masach)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spCuonSach_DemCuonSach";
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = masach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaSach);
            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm mã cuốn sách
        //----------------------------------------------------------------------
        public int DemMaCuonSach(string macuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spCuonSach_DemMaCuonSach";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaCuonSach);
            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật cuốn sách
        //----------------------------------------------------------------------
        public bool CapNhat(DTOCuonSach cuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spCuonSach_CapNhatCuonSach";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 1 };
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = cuonsach.MaCuonSach };
            SqlParameter sqlprMaSach = new SqlParameter("@MaSach", SqlDbType.VarChar, 15) { Value = cuonsach.MaSach };
            SqlParameter sqlprTinhTrang = new SqlParameter("@TinhTrang", SqlDbType.Bit) { Value = cuonsach.TinhTrang };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaCuonSach,
                sqlprMaSach, sqlprTinhTrang);
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật tình trạng cuốn sách
        //----------------------------------------------------------------------
        public bool CapNhatTinhTrang(string macuonsach, bool tinhtrang)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spCuonSach_CapNhatTinhTrang";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            SqlParameter sqlprTinhTrang = new SqlParameter("@TinhTrang", SqlDbType.Bit) { Value = tinhtrang };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprMaCuonSach, sqlprTinhTrang);
        }

        //----------------------------------------------------------------------
        //Desc: lấy tên đầu sách
        //----------------------------------------------------------------------
        public string LayTenDauSach(string macuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spCuonSach_LayTenDauSach";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaCuonSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            return data.Rows[0][0].ToString();
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách tác giả
        //----------------------------------------------------------------------
        public List<DTOTacGia> LayDSTacGia(string macuonsach)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spCuonSach_LayDanhSachTacGia";
            SqlParameter sqlprMaCuonSach = new SqlParameter("@MaCuonSach", SqlDbType.VarChar, 15) { Value = macuonsach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaCuonSach);
            if (data == null)
                return null;
            List<DTOTacGia> list = new List<DTOTacGia>();
            for (int i = 0; i < data.Rows.Count; i++)
                list.Add(new DTOTacGia(data.Rows[i]["MaTacGia"].ToString(), data.Rows[i]["TenTacGia"].ToString()));
            
            return list;
        }
    }
}
